package com.example.demo_text;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EnrollmentDAO {

    /**
     * 获取所有选课记录
     */
    public static List<StudentEnrollment> getAllEnrollments() {
        List<StudentEnrollment> list = new ArrayList<>();
        String sql = "SELECT sc.user_id, sc.course_id, u.username AS user_name, c.course_name " +
                "FROM student_course sc " +
                "JOIN login u ON sc.user_id = u.id " +
                "JOIN course c ON sc.course_id = c.course_id";

        try (Connection conn = UserDAO.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                StudentEnrollment se = new StudentEnrollment();
                se.setUserId(rs.getInt("user_id"));
                se.setCourseId(rs.getInt("course_id"));
                se.setUserName(rs.getString("user_name"));
                se.setCourseName(rs.getString("course_name"));
                list.add(se);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据用户ID获取其选课记录
     */
    public static List<StudentEnrollment> getEnrollmentsByUserId(int userId) {
        List<StudentEnrollment> list = new ArrayList<>();
        String sql = "SELECT sc.user_id, sc.course_id, u.username AS user_name, c.course_name " +
                "FROM student_course sc " +
                "JOIN login u ON sc.user_id = u.id " +
                "JOIN course c ON sc.course_id = c.course_id " +
                "WHERE sc.user_id = ?";

        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    StudentEnrollment se = new StudentEnrollment();
                    se.setUserId(rs.getInt("user_id"));
                    se.setCourseId(rs.getInt("course_id"));
                    se.setUserName(rs.getString("user_name"));
                    se.setCourseName(rs.getString("course_name"));
                    list.add(se);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加选课记录
     */
    public static boolean addEnrollment(int userId, int courseId) {
        String sql = "INSERT INTO student_course(user_id, course_id) VALUES (?, ?)";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, courseId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除选课记录
     */
    public static boolean deleteEnrollment(int userId, int courseId) {
        String sql = "DELETE FROM student_course WHERE user_id = ? AND course_id = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, courseId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean deleteAllEnrollmentsByUserId(int userId) {
        String sql = "DELETE FROM student_course WHERE user_id = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}
